package com.vimi8.ebb.service;

import com.vimi8.ebb.dto.*;
import com.vimi8.ebb.model.ElectricityBusiness;
import com.vimi8.ebb.model.ProductInfo;
import com.vimi8.ebb.param.PlatformStatisticsParam;
import com.vimi8.ebb.sqlprovider.ElectricityBusinessSqlProvider;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.IntegerTypeHandler;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by wangyang on 2017/1/19.
 */
@Mapper
public interface ElectricityBusinessService {

    @Insert("insert into electricity_business (id,store_name,platform_category,store_category_id,type,address_code," +
            "address_detail,responsible_person,mobile_no,employee_num,store_url,longitude,latitude,create_time," +
            "update_time) values(#{id},#{storeName},#{platformCategory},#{storeCategoryId},#{type},#{addressCode}," +
            "#{addressDetail},#{responsiblePerson},#{mobileNo},#{employeeNum},#{storeUrl},#{longitude},#{latitude}," +
            "CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP())")
    public int registerElectricityBusiness(ElectricityBusiness electricityBusiness);

    @InsertProvider(type = ElectricityBusinessSqlProvider.class, method = "getInsertElectricityBusinessTypeSql")
    public int batchRegisterElectricityBusiness(@Param("electricityBusinessList") List<ElectricityBusiness> electricityBusinessList);

    @Delete("delete from electricity_business where id=#{id}")
    public int deleteElectricityBusiness(@Param("id") String id);

    /**
     * 根据所选电商ids批量删除数据
     * @param ids
     * @return
     */
    @Delete("DELETE e,p FROM electricity_business AS e LEFT JOIN product_info AS p ON " +
            "e.id=p.electricity_business_id WHERE e.id IN (${ids})")
    public Integer deleteSelectElectricityBusiness(@Param("ids") String ids);


    @Update("update electricity_business set store_name=#{storeName},platform_category_parent=#{platformCategoryParent},platform_category=#{platformCategory}," +
            "store_category_id=#{storeCategoryId},type=#{type},address_code=#{addressCode},address_detail=#{addressDetail}," +
            "responsible_person=#{responsiblePerson},mobile_no=#{mobileNo},employee_num=#{employeeNum},store_url=#{storeUrl}," +
            "longitude=#{longitude},latitude=#{latitude},update_time=CURRENT_TIMESTAMP()  where id=#{id}")
    public int updateElectricityBusiness(ElectricityBusinessDto electricityBusiness);
    @Update("update electricity_business set store_name=#{storeName},platform_category_parent=#{platformCategoryParent},platform_category=#{platformCategory}," +
            "store_category_id=#{storeCategoryId},type=#{type},address_code=#{addressCode},address_detail=#{addressDetail}," +
            "responsible_person=#{responsiblePerson},mobile_no=#{mobileNo},employee_num=#{employeeNum},store_url=#{storeUrl}," +
            "longitude=#{longitude},latitude=#{latitude},update_time=CURRENT_TIMESTAMP(),is_crawlinged=0,remark=#{remark}  where id=#{id}")
    public int updateEbbUrl(ElectricityBusinessDto electricityBusiness);
    @Select("select count(1) from electricity_business  where id=#{id} and store_url!=#{storeUrl}")
    public int queryElectricityBusinessUrl(@Param("id") String id, @Param("storeUrl") String storeUrl);

    /**
     * 根据type查询电商经营主体总数
     * @param type
     * @return
     */
    @Select("<script>select COUNT(DISTINCT(e.id)) from electricity_business as e LEFT JOIN product_info as p on " +
            "p.electricity_business_id=e.id where type=#{type}" +
            " <if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"time!=null and time!='' \"> and p.trading_time  like CONCAT('%',#{time},'%')</if>" +
            " <if test=\"code!=null and code!='' \"> and e.address_code like CONCAT('%',#{code},'%')</if></script>")
    public int getElectricityBusinessCountByType(@Param("type") String type,
                                                 @Param("storeName") String storeName,
                                                 @Param("code") String code,
                                                 @Param("time") String time);

    /**
     * 根据type查询电商经营主体总数 Admin
     * @param type
     * @return
     */
    @Select("<script>SELECT COUNT(t.id) FROM (select e.id as id " +
            "FROM electricity_business e " +
            "LEFT JOIN product_info as p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"pick!=null and pick!='' \"> AND e.is_crawlinged = #{pick} </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> and p.`trading_time` &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and p.`trading_time` &lt;= #{endTime} </if> " +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and e.platform_category = #{platformCategory} </if> " +
            "<if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%') </if> " +
            "GROUP BY e.id) as t  " +
            "</script>")
    public int getElectricityBusinessCountByTypeByAdmin(@Param("type") String type,
                                                        @Param("storeName") String storeName,
                                                        @Param("code") String code,
                                                        @Param("pick") String pick,
                                                        @Param("platformCategory") String platformCategory,
                                                        @Param("startTime") String startTime,
                                                        @Param("endTime") String endTime);


    /**
     * 根据type查询无区县电商经营主体总数 Admin
     * @param type
     * @return
     */
    @Select("<script>SELECT COUNT(t.id) FROM (select e.id as id " +
            "FROM electricity_business e " +
            "LEFT JOIN product_info as p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type}  " +
            "<if test=\"code!=null and code!='' \"> and address_code like CONCAT(#{code},'%') </if> " +
            "GROUP BY e.id) as t  " +
            "</script>")
    public int getNoRegionElectricityBusinessCountByTypeByAdmin(@Param("type") String type,@Param("code") String code);


    /**
     * 根据type查询店铺总数
     * @param type
     * @return
     */
    @Select("<script>select COUNT(DISTINCT(e.id)) from electricity_business  AS e LEFT JOIN trading_detail AS t ON " +
            "t.`ebb_id` = e.`id` where type=#{type}" +
            " <if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"time!=null and time!='' \"> and e.create_time  like CONCAT('%',#{time},'%')</if>" +
            " <if test=\"code!=null and code!='' \"> and e.address_code like CONCAT('%',#{code},'%')</if></script>")
    public int getStoreElectricityBusinessCountByType(@Param("type") String type,
                                                      @Param("storeName") String storeName,
                                                      @Param("code") String code,
                                                      @Param("time") String time);

    /**
     * 根据type查询店铺总数 Admin
     * @param type
     * @return
     */
    @Select("<script>select COUNT(DISTINCT(e.id)) from electricity_business  AS e LEFT JOIN trading_detail AS t ON t.`ebb_id` = e.`id` WHERE type=#{type}" +
            " <if test=\"storeName!=null and storeName!='' \"> AND e.store_name LIKE CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"pick!=null and pick!='' \"> AND e.is_crawlinged = #{pick} </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND t.trade_date &gt; #{startTime} </if> " +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and e.platform_category = #{platformCategory} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND t.trade_date &lt;= #{endTime} </if> " +
            " <if test=\"code!=null and code!='' \"> AND e.address_code LIKE CONCAT('%',#{code},'%')</if></script>")
    public int getStoreElectricityBusinessCountByTypeByAdmin(@Param("type") String type,
                                                             @Param("storeName") String storeName,
                                                             @Param("code") String code,
                                                             @Param("pick") String pick,
                                                             @Param("platformCategory") String platformCategory,
                                                             @Param("startTime") String startTime,
                                                             @Param("endTime") String endTime);


    /**
     * 根据type查询店铺总数 Admin
     * @param type
     * @return
     */
    @Select("<script>select COUNT(DISTINCT(e.id)) from electricity_business  AS e LEFT JOIN " +
            "trading_detail AS t ON t.`ebb_id` = e.`id` WHERE type=#{type}" +
            " <if test=\"code!=null and code!='' \"> AND e.address_code LIKE CONCAT(#{code},'%')</if></script>")
    public int getStoreNoRegionElectricityBusinessCountByTypeByAdmin(@Param("type") String type,
                                                             @Param("code") String code);



    /**
     * 查询电商经营主体(除服务站点)总数
     * @return
     */
    @Select("<script>select count(1) from electricity_business where type in('1','3') " +
            " <if test=\"storeName!=null and storeName!='' \"> and store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"time!=null and time!='' \"> and create_time  like CONCAT('%',#{time},'%')</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if></script>")
    public int getElectricityBusinessCount(
            @Param("storeName") String storeName,
            @Param("code") String code,
            @Param("time") String time);


    /**
     * 根据type查询电商经营主体总数
     * @param type
     * @return
     * DATE_FORMAT 将日期解析成年月分
     */
    @Select("<script>" +
            "SELECT COUNT(DISTINCT(e.id)) " +
            "FROM electricity_business as e " +
            "LEFT JOIN product_info as p ON p.electricity_business_id=e.id " +
            "WHERE type=#{type} " +
            "<if test=\"storeName!=null and storeName!='' \"> and store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> and p.trading_time &gt;= #{startTime}  </if>" +
            "<if test=\"endTime!=null and endTime!='' \"> and p.trading_time &lt;= #{endTime} </if>" +
            "<if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%') </if>" +
            "</script>")
    public int getElectricityBusinessCountByType1(@Param("type") String type,
                                                  @Param("storeName") String storeName,
                                                  @Param("code") String code,
                                                  @Param("startTime") String startTime,
                                                  @Param("endTime") String endTime);

    /**
     * 根据type查询top店铺表格总数
     * @param type
     * @return
     * DATE_FORMAT 将日期解析成年月分
     */
    @Select("<script>select COUNT(DISTINCT(e.id)) from electricity_business as e left join trading_detail AS t ON t.`ebb_id` = e.`id` where type=#{type}" +
            " <if test=\"storeName!=null and storeName!='' \"> and store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"startTime!=null and startTime!='' \"> and create_time &gt;= #{startTime}</if>" +
            "<if test=\"endTime!=null and endTime!='' \"> and create_time &lt;= #{endTime}</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> </script>")
    public int getStoreElectricityBusinessCountByType1(@Param("type") String type,
                                                       @Param("storeName") String storeName,
                                                       @Param("code") String code,
                                                       @Param("startTime") String startTime,
                                                       @Param("endTime") String endTime);

    /**
     * 根据type查询电商经营主体总数
     * @return
     * DATE_FORMAT 将日期解析成年月分
     */
    @Select("<script>SELECT COUNT(DISTINCT(t.id)) FROM " +
            "            (SELECT e.id AS id,e.store_name AS storeName,e.platform_category AS platformCategory,e.store_category_id " +
            "                        AS storeCategoryId,e.type AS TYPE,e.address_code AS addressCode,e.address_detail AS addressDetail,e.responsible_person " +
            "                        AS responsiblePerson,e.mobile_no AS mobileNo,e.employee_num AS employeeNum,e.store_url AS storeUrl" +
            "                        ,e.longitude AS" +
            "                        longitude,e.latitude AS latitude,p.trading_time AS tradingTime,e.update_time AS updateTime" +
            "                        ,IFNULL(SUM(p.`business_volume`),0) AS businessVolume," +
            "                        IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume " +
            "                        FROM electricity_business e" +
            "                        " +
            "               LEFT JOIN " +
            "                product_info p" +
            "                        ON e.id=p.`electricity_business_id` " +
            "            <if test=\"startTime!=null and startTime!='' \"> and DATE_FORMAT(p.trading_time,'%Y-%m')= #{startTime}</if>" +
            "                         GROUP BY p.`electricity_business_id` ORDER BY tradingVolume DESC " +
            "                         ) AS t" +
            "                         WHERE t.`type` IN('1','3') " +
            "            <if test=\"storeName!=null and storeName!='' \"> and t.storeName like CONCAT('%',#{storeName},'%')</if>" +
            "             <if test=\"code!=null and code!='' \"> and t.addressCode like CONCAT('%',#{code},'%')</if></script>")
    public int getElectricityBusinessCountExceptSit(
            @Param("storeName") String storeName,
            @Param("code") String code,
            @Param("startTime") String startTime,
            @Param("endTime") String endTime);

    /**
     * 根据type查询电商经营主体列表
     * @param type
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>" +
            "SELECT e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id as storeCategoryId," +
            "e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person as responsiblePerson," +
            "e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,SUM(p.business_volume) as businessVolume," +
            "ROUND(IFNULL(SUM(p.trading_volume),0),2) as tradingVolume,e.longitude as longitude,e.latitude as latitude," +
            "e.create_time as createTime,e.update_time as updateTime,ec.category_name as storeCategory " +
            "FROM electricity_business e " +
            "LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN product_info as p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"time!=null and time!='' \"> and p.trading_time  like CONCAT('%',#{time},'%') </if>" +
            "<if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%') </if> " +
            "GROUP BY e.id ORDER BY tradingVolume DESC,createTime DESC " +
            "LIMIT #{start},#{size}" +
            "</script>")
    public List<ElectricityBusinessDto> getElectricityBusinessList(@Param("type") String type, @Param("storeName") String storeName,
                                                                   @Param("code") String code,
                                                                   @Param("time") String time,
                                                                   @Param("start") int start, @Param("size") int size);





    /**
     * 根据type查询电商经营主体列表 Admin
     * @param type
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>" +
            "SELECT e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id as storeCategoryId," +
            "e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person as responsiblePerson," +
            "e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,IFNULL(SUM(p.business_volume),0) as businessVolume," +
            "ROUND(IFNULL(SUM(p.trading_volume),0),2) as tradingVolume,e.longitude as longitude,e.latitude as latitude," +
            "p.trading_time as createTime,e.update_time as updateTime,e.is_crawlinged as isCrawlinged,e.remark as remark  " +
            "FROM electricity_business e " +
            "LEFT JOIN product_info as p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> AND e.store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"pick!=null and pick!='' \"> AND e.is_crawlinged = #{pick} </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND p.trading_time &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND p.trading_time &lt;= #{endTime} </if> " +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and e.platform_category = #{platformCategory} </if> " +
            "<if test=\"code!=null and code!='' \"> AND address_code like CONCAT('%',#{code},'%') </if> " +
            "GROUP BY e.id ORDER BY p.trading_time DESC " +
            "LIMIT #{start},#{size}" +
            "</script>")
    public List<ElectricityBusinessDto> getElectricityBusinessListAdmin(@Param("type") String type,
                                                                        @Param("storeName") String storeName,
                                                                        @Param("code") String code,
                                                                        @Param("pick") String pick,
                                                                        @Param("platformCategory") String platformCategory,
                                                                        @Param("startTime") String startTime,
                                                                        @Param("endTime") String endTime,
                                                                        @Param("start") int start, @Param("size") int size);


    /**
     * 根据type查询无区县电商经营主体列表 Admin
     * @param type
     * @param start
     * @param size
     * @return
     */
    @Select("<script>" +
            "SELECT e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id as storeCategoryId," +
            "e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person as responsiblePerson," +
            "e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,IFNULL(SUM(p.business_volume),0) as businessVolume," +
            "ROUND(IFNULL(SUM(p.trading_volume),0),2) as tradingVolume,e.longitude as longitude,e.latitude as latitude," +
            "p.trading_time as createTime,e.update_time as updateTime,e.is_crawlinged as isCrawlinged,e.remark as remark  " +
            "FROM electricity_business e " +
            "LEFT JOIN product_info as p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type}  " +
            "<if test=\"code!=null and code!='' \"> AND address_code like CONCAT(#{code},'%') </if> " +
            "GROUP BY e.id ORDER BY p.trading_time DESC " +
            "LIMIT #{start},#{size}" +
            "</script>")
    public List<ElectricityBusinessDto> getNoRegionElectricityBusinessListAdmin(@Param("type") String type,
                                                                        @Param("code") String code,
                                                                        @Param("start") int start, @Param("size") int size);


    /**
     * 根据type（1,3）查询电商经营主体总交易量，总交易额 Admin
     * @param type
     * @param storeName
     * @return
     */
    @Select("<script>" +
            "SELECT IFNULL(SUM(p.business_volume),0) AS totalBusinessVolume," +
            "ROUND(IFNULL(SUM(p.trading_volume),0),2) AS totalTradingVolume " +
            "FROM electricity_business e " +
            "LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN product_info AS p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> AND e.store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"pick!=null and pick!='' \"> AND e.is_crawlinged = #{pick} </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND p.trading_time &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND p.trading_time &lt;= #{endTime} </if> " +
            "<if test=\"code!=null and code!='' \"> AND address_code LIKE CONCAT(#{code},'%') </if> " +//
            "</script>")
    public HashMap<String,Object> getElectricityBusinessTransactionAmountAdmin(@Param("type") String type, @Param("storeName") String storeName,
                                                                               @Param("code") String code,
                                                                               @Param("pick") String pick,
                                                                               @Param("startTime") String startTime,
                                                                               @Param("endTime") String endTime);


    /**
     * 根据type（1,3）查询电商经营主体无法爬取数据的店铺量，总交易额 Admin
     * @param type
     * @param storeName
     * @return
     */
    @Select("<script>" +
            "SELECT IFNULL(COUNT(e.id),0) " +
            "FROM electricity_business e " +
            "LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN product_info AS p ON p.electricity_business_id=e.id " +
            "WHERE e.type=#{type} AND e.is_crawlinged = -1 " +
            "<if test=\"storeName!=null and storeName!='' \"> AND e.store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND p.trading_time &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND p.trading_time &lt;= #{endTime} </if> " +
            "<if test=\"code!=null and code!='' \"> AND address_code LIKE CONCAT(#{code},'%') </if> " +//
            "</script>")
    public int getUnPickAmountAdmin(@Param("type") String type, @Param("storeName") String storeName,
                                    @Param("code") String code,
                                    @Param("startTime") String startTime,
                                    @Param("endTime") String endTime);


    /**
     * 根据type（2,4）查询电商经营主体总交易量，总交易额 Admin
     * @param type
     * @param storeName
     * @return
     */
    @Select("<script>" +
            "SELECT IFNULL(SUM(t.business_volume),0) AS totalBusinessVolume," +
            "ROUND(IFNULL(SUM(t.trading_volume),0),2) AS totalTradingVolume " +
            "FROM electricity_business e " +
            "LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN trading_detail AS t ON t.`ebb_id` = e.`id` " +
            "WHERE e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> AND e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"pick!=null and pick!='' \"> AND e.is_crawlinged = #{pick} </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND t.trade_date &gt; #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND t.trade_date &lt;= #{endTime} </if> " +
            "<if test=\"code!=null and code!='' \"> AND address_code LIKE CONCAT(#{code},'%') </if> " +
            "</script>")
    public HashMap<String,Object> getStoreElectricityBusinessTransactionAmountAdmin(@Param("type") String type, @Param("storeName") String storeName,
                                                                                    @Param("code") String code,
                                                                                    @Param("pick") String pick,
                                                                                    @Param("startTime") String startTime,
                                                                                    @Param("endTime") String endTime);

    /**
     * 根据type（2,4）查询电商经营主体无法爬取数据的店铺量，总交易额 Admin
     * @param type
     * @param storeName
     * @return
     */
    @Select("<script>" +
            "SELECT IFNULL(COUNT(e.id),0) " +
            "FROM electricity_business e " +
            "LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN trading_detail AS t ON t.`ebb_id` = e.`id` " +
            "WHERE e.type=#{type}  AND e.is_crawlinged = -1 " +
            "<if test=\"storeName!=null and storeName!='' \"> AND e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND t.trade_date &gt; #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND t.trade_date &lt;= #{endTime} </if> " +
            "<if test=\"code!=null and code!='' \"> AND address_code LIKE CONCAT(#{code},'%') </if> " +
            "</script>")
    public int getStoreUnPickAmountAdminAmountAdmin(@Param("type") String type, @Param("storeName") String storeName,
                                                    @Param("code") String code,
                                                    @Param("startTime") String startTime,
                                                    @Param("endTime") String endTime);




    /**
     * 根据type查询电商经营主体列表
     * @param type
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>select e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,SUM(t.trading_volume) as tradingVolume" +
            ",e.longitude as longitude,e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name as " +
            "storeCategory from electricity_business e join enum_category ec on e.store_category_id=ec.id " +
            "left join trading_detail AS t ON t.`ebb_id` = e.`id` where e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"time!=null and time!='' \"> and e.create_time  like CONCAT('%',#{time},'%')</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> GROUP BY e.id ORDER BY t.trading_volume DESC,createTime DESC limit #{start},#{size}</script>")
    public List<ElectricityBusinessDto> getStoreElectricityBusinessList(@Param("type") String type, @Param("storeName") String storeName,
                                                                        @Param("code") String code,
                                                                        @Param("time") String time,
                                                                        @Param("start") int start, @Param("size") int size);


    /**
     * 根据type查询电商经营主体列表 Admin
     * @param type
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>select e.id as id,e.create_time as createTime,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.`type` as `type`,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,IFNULL(SUM(t.trading_volume),0) AS tradingVolume," +
            "IFNULL(SUM(t.business_volume),0) AS businessVolume," +
            "e.longitude as longitude,e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name AS " +
            "storeCategory FROM electricity_business e LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN trading_detail AS t ON t.`ebb_id` = e.`id` WHERE e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> AND e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"pick!=null and pick!='' \"> AND e.is_crawlinged = #{pick} </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> AND t.trade_date &gt; #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> AND t.trade_date &lt;= #{endTime} </if> " +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and e.platform_category = #{platformCategory} </if> " +
            " <if test=\"code!=null and code!='' \"> AND e.address_code like CONCAT('%',#{code},'%')</if> " +
            "GROUP BY e.id " +
            "ORDER BY t.trade_date DESC limit #{start},#{size}" +
            "</script>")
    public List<ElectricityBusinessDto> getStoreElectricityBusinessListAdmin(@Param("type") String type,
                                                                             @Param("storeName") String storeName,
                                                                             @Param("code") String code,
                                                                             @Param("pick") String pick,
                                                                             @Param("platformCategory") String platformCategory,
                                                                             @Param("startTime") String startTime,
                                                                             @Param("endTime") String endTime,
                                                                             @Param("start") int start, @Param("size") int size);



    /**
     * 根据type查询电商经营主体列表 Admin
     * @param type
     * @param start
     * @param size
     * @return
     */
    @Select("<script>select e.id as id,e.create_time as createTime,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.`type` as `type`,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,IFNULL(SUM(t.trading_volume),0) AS tradingVolume," +
            "IFNULL(SUM(t.business_volume),0) AS businessVolume," +
            "e.longitude as longitude,e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name AS " +
            "storeCategory FROM electricity_business e LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "LEFT JOIN trading_detail AS t ON t.`ebb_id` = e.`id` WHERE e.type=#{type}  " +
            " <if test=\"code!=null and code!='' \"> AND e.address_code like CONCAT(#{code},'%')</if> " +
            "GROUP BY e.id " +
            "ORDER BY t.trade_date DESC limit #{start},#{size}" +
            "</script>")
    public List<ElectricityBusinessDto> getStoreNoRegionElectricityBusinessListAdmin(@Param("type") String type,
                                                                             @Param("code") String code,
                                                                             @Param("start") int start, @Param("size") int size);



    /**
     * 查询电商经营主体列表(除服务站点和本地服务平台)
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>select e.id as id,e.store_name as storeName,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl" +
            ",e.longitude as " +
            "longitude,e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name as " +
            "storeCategory,IFNULL(SUM(p.business_volume),0) AS businessVolume,IFNULL(SUM(p.trading_volume),0) AS tradingVolume from electricity_business e " +
            " LEFT JOIN enum_category ec ON e.store_category_id=ec.id " +
            "  LEFT JOIN product_info AS p ON p.electricity_business_id=e.id "+
            " where e.type in('1','3') " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"time!=null and time!='' \"> and e.create_time  like CONCAT('%',#{time},'%')</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> order by tradingVolume desc limit #{start},#{size}</script>")
    public List<ElectricityBusinessDto> getElectricityBusiness(@Param("storeName") String storeName,
                                                               @Param("code") String code,
                                                               @Param("time") String time,
                                                               @Param("start") int start, @Param("size") int size);

    /**
     * 根据type查询电商经营主体列表
     * @param type
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>" +
            "SELECT e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl" +
            ",e.longitude as longitude,SUM(p.business_volume) as businessVolume,ROUND(IFNULL(SUM(p.trading_volume),0),2) as tradingVolume" +
            "e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name as storeCategory " +
            "FROM electricity_business e " +
            "LEFT JOIN enum_category ec on e.store_category_id=ec.id " +
            "LEFT JOIN product_info as p on p.electricity_business_id=e.id where e.type=#{type} " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%') </if>" +
            "<if test=\"startTime!=null and startTime!='' \"> and p.trading_time &gt;= #{startTime} </if>" +
            "<if test=\"endTime!=null and endTime!='' \"> and p.trading_time &lt;= #{endTime} </if>" +
            "<if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%') </if> " +
            "GROUP BY e.id ORDER BY tradingVolume DESC,createTime DESC " +
            "limit #{start},#{size}" +
            "</script>")
    public List<ElectricityBusinessDto> getElectricityBusinessList1(@Param("type") String type, @Param("storeName") String storeName,
                                                                    @Param("code") String code,
                                                                    @Param("startTime") String startTime,
                                                                    @Param("endTime") String endTime,
                                                                    @Param("start") int start, @Param("size") int size);

    /**
     * 根据type查询店铺top10列表
     * @param type
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>select e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl" +
            ",e.longitude as longitude,SUM(t.trading_volume) as tradingVolume" +
            "e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name as " +
            "storeCategory from electricity_business e join enum_category ec on e.store_category_id=ec.id " +
            "left join trading_detail AS t ON t.`ebb_id` = e.`id` where e.type=#{type}" +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"startTime!=null and startTime!='' \"> and create_time &gt;= #{startTime}</if>" +
            "<if test=\"endTime!=null and endTime!='' \"> and create_time &lt;= #{endTime}</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> GROUP BY e.id ORDER BY t.trading_volume DESC,createTime DESC limit #{start},#{size}</script>")
    public List<ElectricityBusinessDto> getStoreElectricityBusinessList1(@Param("type") String type, @Param("storeName") String storeName,
                                                                         @Param("code") String code,
                                                                         @Param("startTime") String startTime,
                                                                         @Param("endTime") String endTime,
                                                                         @Param("start") int start, @Param("size") int size);

    /**
     * 根据type查询电商经营主体列表
     * @param storeName
     * @param start
     * @param size
     * @return
     */
    @Select("<script>SELECT  t.id,t.storeName,t.platformCategory,t.storeCategoryId,t.TYPE,t.addressCode,t.addressDetail,t.responsiblePerson,t.mobileNo,t.employeeNum,t.storeUrl," +
            "t.longitude,t.latitude,t.tradingTime,t.businessVolume,t.tradingVolume,FORMAT(t.tradingVolume,2) as tradingVolume FROM " +
            "(SELECT e.id AS id,e.store_name AS storeName,e.platform_category AS platformCategory,e.store_category_id " +
            "            AS storeCategoryId,e.type AS TYPE,e.address_code AS addressCode,e.address_detail AS addressDetail,e.responsible_person " +
            "            AS responsiblePerson,e.mobile_no AS mobileNo,e.employee_num AS employeeNum,e.store_url AS storeUrl" +
            "            ,e.longitude AS" +
            "            longitude,e.latitude AS latitude,p.trading_time AS tradingTime,e.update_time AS updateTime" +
            "            ,IFNULL(SUM(p.`business_volume`),0) AS businessVolume," +
            "            IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume " +
            "            FROM electricity_business e" +
            "            " +
            "   LEFT JOIN " +
            "    product_info p" +
            "            ON e.id=p.`electricity_business_id` " +
            "<if test=\"startTime!=null and startTime!='' \"> and DATE_FORMAT(p.trading_time,'%Y-%m')= #{startTime}</if>" +
            "             GROUP BY p.`electricity_business_id` ORDER BY tradingVolume DESC " +
            "             ) AS t" +
            "             WHERE t.`type` IN('1','3') " +
            "<if test=\"storeName!=null and storeName!='' \"> and t.storeName like CONCAT('%',#{storeName},'%')</if>" +
            " <if test=\"code!=null and code!='' \"> and t.addressCode like CONCAT('%',#{code},'%')</if> limit #{start},#{size}</script>")
    public List<ElectricityBusinessDto> getElectricityBusinessExceptSit(@Param("storeName") String storeName,
                                                                        @Param("code") String code,
                                                                        @Param("startTime") String startTime,
                                                                        @Param("endTime") String endTime,
                                                                        @Param("start") int start, @Param("size") int size);

    //查询交易量和交易额
    @Select(" select IFNULL(sum(p.business_volume),0) as businessVolume,IFNULL(sum(p.trading_volume),0) as tradingVolume from  product_info as p where" +
            " p.electricity_business_id  = #{id}")
    public HashMap<String,Object> getBusinessAndTrandingByEleId(@Param("id") String id);

    //查询电商交易额
    @Select("select IFNULL(sum(trading_volume),0) from trading_detail where  ebb_id=#{id}")
    public float getTradingByEleId(@Param("id") String id);


//    /**
//     * 查询导出的平台信息
//     * @param type
//     * @param storeName
//     * @param
//     * @param
//     * @return
//     */
//    @Select("<script>select e.id as id,e.store_name as storeName,e.platform_category as platformCategory,e.store_category_id " +
//            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
//            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl" +
//            ",e.longitude as " +
//            "longitude,e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name as " +
//            "storeCategory from electricity_business as e join " +
//            "enum_category as ec on e.store_category_id=ec.id where e.type=#{type} " +
//            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
//            "<if test=\"time!=null and time!='' \"> and e.create_time  like CONCAT('%',#{time},'%')</if>" +
//            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> </script>")
//    public List<ElectricityBusinessDto> getElectricityBusinessListExport(@Param("type")String type,
//                                                                         @Param("storeName")String storeName,
//                                                                         @Param("code")String code,
//                                                                         @Param("time") String time);



    /**
     * 导出电商经营主体列表
     * @param type
     * @param storeName
     * @param
     * @param
     * @return
     */
    @Select("<script>select e.id as id,e.store_name as storeName,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl," +
            "SUM(p.business_volume) as businessVolume,SUM(p.trading_volume) as tradingVolume" +
            ",e.longitude as longitude,e.latitude as latitude,p.trading_time as createTime,e.update_time as updateTime from electricity_business e " +
            "left join product_info as p on p.electricity_business_id=e.id where e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"startTime!=null and startTime!='' \"> and p.trading_time  &gt;=#{startTime}</if>" +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and platform_category = #{platformCategory} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and p.trading_time  &lt;#{endTime}</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> GROUP BY e.id ORDER BY e.`create_time` DESC </script>")
    public List<ElectricityBusinessDto> getElectricityBusinessListExort(@Param("type") String type, @Param("storeName") String storeName,
                                                                        @Param("code") String code,
                                                                        @Param("platformCategory") String platformCategory,
                                                                        @Param("startTime") String startTime,
                                                                        @Param("endTime") String endTime);







    /**
     * 根据type查询电商经营主体列表
     * @param type
     * @param storeName
     * @param
     * @param
     * @return
     */
    @Select("<script>select e.id as id,e.store_name as storeName,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl,SUM(t.trading_volume) as tradingVolume" +
            ",e.longitude as longitude,e.latitude as latitude,t.trade_date as createTime,e.update_time as updateTime from electricity_business e " +
            "left join trading_detail AS t ON t.`ebb_id` = e.`id` where e.type=#{type}  " +
            "<if test=\"storeName!=null and storeName!='' \"> and e.store_name like CONCAT('%',#{storeName},'%')</if>" +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and platform_category = #{platformCategory} </if> " +
            "<if test=\"startTime!=null and startTime!='' \"> and t.trade_date  &gt;=#{startTime}</if>" +
            "<if test=\"endTime!=null and endTime!='' \"> and t.trade_date  &lt;#{endTime}</if>" +
            " <if test=\"code!=null and code!='' \"> and address_code like CONCAT('%',#{code},'%')</if> GROUP BY e.id ORDER BY t.trading_volume DESC </script>")
    public List<ElectricityBusinessDto> getStoreElectricityBusinessListExport(@Param("type") String type, @Param("storeName") String storeName,
                                                                              @Param("code") String code,
                                                                              @Param("platformCategory") String platformCategory,
                                                                              @Param("startTime") String startTime,
                                                                              @Param("endTime") String endTime);









    /**
     * 根据id查询电商经营主体
     * @param id
     * @return
     */
    @Select("select e.id as id,e.store_name as storeName,e.platform_category_parent as platformCategoryParent,e.platform_category as platformCategory,e.store_category_id " +
            "as storeCategoryId,e.type as type,e.address_code as addressCode,e.address_detail as addressDetail,e.responsible_person " +
            "as responsiblePerson,e.mobile_no as mobileNo,e.employee_num as employeeNum,e.store_url as storeUrl, " +
            "e.longitude as longitude,e.latitude as latitude,e.create_time as createTime,e.update_time as updateTime,ec.category_name as " +
            "storeCategory from electricity_business e left join " +
            "enum_category ec on e.store_category_id=ec.id where e.id=#{id}")
    public ElectricityBusinessDto getElectricityBusinessById(@Param("id") String id);

    @Insert("insert into product_info(id,product_name,enum_category_id,unit_price,electricity_business_id,stock_volume,stock_unit)" +
            " values(#{id},#{productName},#{enumCategoryId},#{unitPrice},#{electricityBusinessId},#{stockVolume},#{stockUnit})")
    public int insertElectricityBusinessProduct(ProductInfo productInfo);

    //查询各区域销售量
    @Select("SELECT SUM(p.business_volume) AS businessVolume FROM" +
            "  product_info as p join electricity_business as e on p.`electricity_business_id` = e.`id`  WHERE DATE_FORMAT(p.trading_time,'%Y-%m')=#{time}  and " +
            "e.address_code like CONCAT('%',#{addressCode},'%')")
    public HashMap<String,Object> getPlatformManagement(@Param("time") String time, @Param("addressCode") String addressCode);

    //查询区域交易量
    @Select("SELECT SUM(p.trading_volume) AS tradingVolume FROM  product_info as p join electricity_business as e on" +
            " p.`electricity_business_id` = e.`id`  WHERE DATE_FORMAT(p.trading_time,'%Y-%m')=#{time}  and e.address_code like CONCAT('%',#{addressCode},'%')")
    public HashMap<String,Object> getPlatformManagement2(@Param("time") String time, @Param("addressCode") String addressCode);


    //查询区域交易量
    @Select("SELECT IFNULL(count(1),0) as num FROM  electricity_business as e  WHERE e.`type` in(1,3,4) AND e.address_code like CONCAT('%',#{addressCode},'%')")
    public Integer getRegionPlatformCount(@Param("addressCode") String addressCode);



    //查询最近导入十条数据
    @Select("select e.id,e.`platform_category` as platFormName,p.trading_volume as tradingVolume,store_url as storeUrl,p.`product_name` as productName," +
            " p.`enum_category_id` as categoryName,p.`trading_time` as createTime from electricity_business as e join product_info as p" +
            " on p.`electricity_business_id` = e.`id` order by p.trading_time DESC,p.`trading_volume` DESC limit 10")
    public List<PlatformBusinessDto> getRelationPlatform();


    //查询站点统计数量
    @Select("SELECT COUNT(1) AS businessVolume,address_code AS addressCode FROM electricity_business WHERE" +
            " `type`=2 and address_code LIKE CONCAT('%',#{addressCode},'%')  GROUP BY address_code")
    public List<RegionManamentDto> getServiceNumTable(@Param("addressCode") String addressCode);


    //查询店铺销售TOP10
    @Select("<script>SELECT e.`address_code` AS addressCode,e.store_name AS storeName,t.businessVolume,t.tradingVolume FROM electricity_business e LEFT JOIN " +
            "  (SELECT IFNULL(SUM(p.`business_volume`),0) AS businessVolume, IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume,p.`electricity_business_id`" +
            "   FROM product_info p   WHERE 1=1 " +
            " <if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if>" +
            "  GROUP BY  p.`electricity_business_id`) AS t ON e.id=t.electricity_business_id WHERE 1=1 " +
            " <if test=\" addressCode !=null and addressCode!='' \"> and e.address_code like concat('%',#{addressCode},'%') </if>" +
            "    ORDER BY tradingVolume DESC LIMIT 10</script>")
    public List<RegionManamentDto> getStoreSalesTop10(@Param("addressCode") String addressCode, @Param("startTime") String startTime);


    //查询店铺销量
    @Select("SELECT SUM(business_volume) AS businessVolume,SUM(trading_volume) AS tradingVolume,trading_time AS tradingTime FROM" +
            " product_info WHERE electricity_business_id=#{id} GROUP BY DATE_FORMAT(trading_time,'%Y-%m') limit #{start},#{size}")
    public List<RegionManamentDto> getStoreSalesInfo(@Param("id") String id,
                                                     @Param("start") int start,
                                                     @Param("size") int size);

    //查询店铺销量总记录数
    @Select("SELECT IFNULL(count(1),0) FROM product_info WHERE electricity_business_id=#{id} GROUP BY DATE_FORMAT(trading_time,'%Y-%m')")
    public Integer getStoreSalesInfoCount(@Param("id") String id);

    //查询各区域销售额同比环比
    @Select(" select sum(p.trading_volume) as tradingVolume from electricity_business as e join product_info as p on p.electricity_business_id=e.id where" +
            " e.address_code BETWEEN #{addressCode} and #{nextAddress} and DATE_FORMAT(create_time,'%Y-%m')=#{time} ")
    public HashMap<String,Object> getRegionSales(@Param("addressCode") String addressCode,
                                                 @Param("nextAddress") long nextAddress,
                                                 @Param("time") String time);


    //获取年销售
    @Select(" select sum(p.trading_volume) as tradingVolume from electricity_business as e join product_info as p on p.electricity_business_id=e.id where " +
            " e.address_code BETWEEN #{addressCode} and #{nextAddress} and DATE_FORMAT(create_time,'%Y')=#{time} ")
    public HashMap<String,Object> getYearSales(@Param("addressCode") String addressCode,
                                               @Param("nextAddress") long nextAddress,
                                               @Param("time") String time);
    //获取月销售
    @Select(" select sum(p.trading_volume) as tradingVolume from electricity_business as e join product_info as p on p.electricity_business_id=e.id where " +
            " e.address_code BETWEEN #{addressCode} and #{nextAddress} and DATE_FORMAT(create_time,'%m')=#{time} ")
    public HashMap<String,Object> getMouthSales(@Param("addressCode") String addressCode,
                                                @Param("nextAddress") long nextAddress,
                                                @Param("time") String time);

    //获取平台总数
    @Select("SELECT IFNULL(COUNT(1),0) AS num FROM electricity_business where `type` in(1,3,4)")
    public Integer getBusinessCount();

    //获取服务站点总数
    @Select("SELECT IFNULL(COUNT(1),0) AS num FROM electricity_business WHERE TYPE=2;")
    public Integer getServiceCount();

    //获取物流总数
    @Select("SELECT IFNULL(COUNT(1),0) AS num FROM logistics")
    public Integer getLogisticsCount();

    //获取培训总数
    @Select("SELECT IFNULL(COUNT(1),0) AS num FROM train_info")
    public Integer getTradInfoCount();

    //获取产品总数
    @Select("SELECT IFNULL(COUNT(1),0) AS num FROM product_info")
    public Integer getProductCount();

    //查询组织的区域
    @Select("select address_code from user_region where user_id=#{clientId}")
    public String getClientAddressCode(@Param("clientId") String clientId);

    //查询服务站点各区域销售量
    @Select("SELECT SUM(td.trading_volume) AS tradingVolume FROM" +
            "  trading_detail as td join electricity_business as e on td.`ebb_id` = e.`id`  WHERE DATE_FORMAT(td.trade_date,'%Y-%m')=#{time} and e.`type`=2 and " +
            "e.address_code like CONCAT('%',#{addressCode},'%')")
    public HashMap<String,Object> getSitPlatformManagement(@Param("time") String time, @Param("addressCode") String addressCode);

    //获取交易汇总信息
    @Select("<script> SELECT e.`platform_category` AS platformCategory,e.`platform_category_parent` AS platformCategoryParent,COUNT(e.`id`) AS productStoreNumber,IFNULL(SUM(p.business_volume),0) AS businessVolume," +
            "IFNULL(SUM(p.trading_volume),0) AS tradingVolume" +
            " FROM electricity_business AS e left JOIN product_info AS p ON p.electricity_business_id=e.id WHERE  type=#{type}" +
            "<if test=\"startTime!=null and startTime!='' \"> and p.`trading_time` &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and p.`trading_time` &lt;= #{endTime} </if> " +
            "<if test=\"addressCode!=null and addressCode!='' \"> and e.`address_code` like concat('%',#{addressCode},'%') </if> " +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and e.`platform_category` =#{platformCategory} </if> " +
            "<if test=\"platformCategoryParent!=null and platformCategoryParent!='' \"> and e.`platform_category_parent` =#{platformCategoryParent} </if> " +
            " </script>")
    public ElectricityBusinessTotalDto getTotalByAddressCode(@Param("addressCode") String addressCode,
                                                             @Param("type") String type,
                                                             @Param("platformCategory") String platformCategory,
                                                             @Param("platformCategoryParent") Integer platformCategoryParent,
                                                             @Param("startTime") String startTime,
                                                             @Param("endTime") String endTime);

    //获取电商交易汇总信息
    @Select("<script> SELECT e.`platform_category` AS platformCategory,e.`platform_category_parent` AS platformCategoryParent,COUNT(e.`id`) AS productStoreNumber,IFNULL(SUM(t.business_volume),0) AS businessVolume," +
            "IFNULL(SUM(t.trading_volume),0) AS tradingVolume" +
            " FROM electricity_business AS e JOIN trading_detail AS t ON t.ebb_id=e.id WHERE  type=#{type}" +
            "<if test=\"addressCode!=null and addressCode!='' \"> and e.`address_code` like concat('%',#{addressCode},'%') </if> " +
            "<if test=\"startTime!=null and startTime!='' \"> and p.`trading_time` &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and p.`trading_time` &lt;= #{endTime} </if> " +
            "<if test=\"platformCategory!=null and platformCategory!='' \"> and e.`platform_category` =#{platformCategory} </if> " +
            "<if test=\"platformCategoryParent!=null and platformCategoryParent!='' \"> and e.`platform_category_parent` =#{platformCategoryParent} </if> " +
            "</script>")
    public ElectricityBusinessTotalDto getEbbTotalByAddressCode(@Param("addressCode") String addressCode,
                                                                @Param("type") String type,
                                                                @Param("platformCategory") String platformCategory,
                                                                @Param("platformCategoryParent") Integer platformCategoryParent,
                                                                @Param("startTime") String startTime,
                                                                @Param("endTime") String endTime);


    //获取店铺数量
    @Select("<script> SELECT IFNULL(COUNT(distinct(e.`id`)),0) AS storeNumber FROM electricity_business AS e  " +
            "WHERE  type=#{type} " +
            "<if test=\"addressCode!=null and addressCode!='' \"> and e.`address_code` like concat('%',#{addressCode},'%') </if> " +
            "<if test=\" platformCategory !=null and platformCategory!='' \"> and e.platform_category like concat('%',#{platformCategory},'%') </if> " +
            "<if test=\" platformCategoryParent !=null and platformCategoryParent!='' \"> and e.platform_category_parent =#{platformCategoryParent} </if> " +
            " </script>")
    public int getStoreNumber(@Param("addressCode") String addressCode, @Param("type") String type, @Param("platformCategory") String platformCategory, @Param("platformCategoryParent") String platformCategoryParent);

    //获取有效店铺数量
    @Select("<script> SELECT IFNULL(COUNT(distinct(e.`id`)),0) AS storeNumber FROM electricity_business AS e join product_info p on e.id=p.electricity_business_id  " +
            "WHERE  type=#{type} " +
            "<if test=\"addressCode!=null and addressCode!='' \"> and e.`address_code` like concat('%',#{addressCode},'%') </if> " +
            "<if test=\" platformCategory !=null and platformCategory!='' \"> and e.platform_category like concat('%',#{platformCategory},'%') </if> " +
            "<if test=\" platformCategoryParent !=null and platformCategoryParent!='' \"> and e.platform_category_parent =#{platformCategoryParent} </if> " +
            "<if test=\"startTime!=null and startTime!='' \"> and p.`trading_time` &gt;= #{startTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \"> and p.`trading_time` &lt;= #{endTime} </if> " +
            " </script>")
    public int getEbbProductStoreNumber(@Param("addressCode") String addressCode, @Param("type") String type, @Param("platformCategory") String platformCategory, @Param("platformCategoryParent") String platformCategoryParent, @Param("startTime") String startTime, @Param("endTime") String endTime);



    //查询店铺销售TOP分析 按时间区间查询
    @Select("<script>SELECT e.address_code AS addressCode,e.store_name AS storeName,t.businessVolume,t.tradingVolume FROM electricity_business e RIGHT JOIN " +
            " (SELECT IFNULL(SUM(p.`business_volume`),0) AS businessVolume, IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume,p.`electricity_business_id`" +
            "  FROM product_info p   WHERE 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if>" +
            " GROUP BY  p.`electricity_business_id`) AS t ON e.id=t.electricity_business_id  where 1=1 " +
            "<if test=\" addressCode !=null and addressCode!='' \"> and e.address_code like concat('%',#{addressCode},'%') </if>" +
            " and e.`type` in(1,3)  ORDER BY tradingVolume DESC LIMIT #{size}</script>")
    public List<RegionManamentDto> getStoreSalesTop(@Param("addressCode") String addressCode, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("size") int size);

    //查询店铺销售TOP分析 按时间区间查询
    @Select("<script>SELECT COUNT(tt.addressCode) AS currentTotal,tt.addressCode FROM " +
            "       ( SELECT e.address_code AS addressCode FROM electricity_business e RIGHT JOIN " +
            "             (" +
            "             SELECT IFNULL(SUM(p.`business_volume`),0) AS businessVolume, IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume,p.`electricity_business_id`" +
            "              FROM product_info p   WHERE 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if>" +
            "           GROUP BY  p.`electricity_business_id`" +
            ") AS t " +
            "             ON e.id=t.electricity_business_id  WHERE 1=1 " +
            "             AND e.`type` IN(1,3) " +
            "              ORDER BY tradingVolume DESC LIMIT #{size}" +
            "       ) AS tt where tt.addressCode=#{addressCode}  GROUP BY tt.addressCode" +
            "     </script>")
    public TopAnalysisDto getTopAnalysisDtoTop(@Param("addressCode") String addressCode, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("size") int size);
    /**
     * 查询电商经营主体(除服务站点)总数
     * @return
     */
    @Select("<script> SELECT count(1) FROM electricity_business e LEFT JOIN " +
            " (SELECT IFNULL(SUM(p.`business_volume`),0) AS businessVolume, IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume,p.`electricity_business_id`" +
            "  FROM product_info p   WHERE 1=1 " +
            "<if test=\"startTime!=null and startTime!='' \">and DATE_FORMAT(p.trading_time,'%Y-%m')=#{startTime}</if>" +
            " GROUP BY  p.`electricity_business_id`) AS t ON e.id=t.electricity_business_id  where 1=1 " +
            "  <if test=\" code !=null and code!='' \"> and e.address_code like concat('%',#{code},'%') </if>" +
            " and e.`type` in(1,3) </script>")
    public int getElectricityBusinessCountByCode(
            @Param("code") String code,
            @Param("startTime") String startTime);

    /**
     * 统计电商平台产品交易信息
     *
     * @param addressCode  地址编码
     * @param yearAndMonth 年-月(如：2017-01)
     * @param year         年度（如：2017）
     * @param quarter      季度（如：1）
     * @return
     */
    @Select("<script>" +
            "SELECT e.platform_category AS platformCategory, count(p.ebbId) AS count, ifnull(TRUNCATE(sum(p.business_volume),2),0) AS businessVolume, ifnull(TRUNCATE(sum(p.trading_volume),2),0) AS tradingVolume " +
            "FROM electricity_business e JOIN (" +
            "   SELECT id, electricity_business_id AS ebbId, business_volume, trading_volume, trading_time AS tradeDate FROM product_info " +
            "   UNION " +
            "   SELECT id, ebb_id AS ebbId, business_volume, trading_volume, trade_date AS tradeDate FROM trading_detail " +
            ") AS p ON e.id=p.ebbId " +
            "<where>" +
            "<if test=\"addressCode !=null and addressCode!=''\"> AND e.address_code LIKE #{addressCode}</if>" +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(p.tradeDate,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(p.tradeDate) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(p.tradeDate) = #{quarter}</if>" +
            "</where>" +
            "GROUP BY e.platform_category" +
            "</script>")
    public List<HashMap<String, Object>> getElectricityBusinessStatistics(
            @Param("addressCode") String addressCode,
            @Param("yearAndMonth") String yearAndMonth,
            @Param("year") String year,
            @Param("quarter") String quarter
    );




    @Select("<script>" +
            "SELECT t1.notInProduct+t4.zeroAmount AS noExistingAmount,t2.existingAmount,t3.averageTurnover FROM" +
            "(SELECT COUNT(*) AS notInProduct FROM  electricity_business  WHERE `type`=1 AND id NOT IN " +
            "(SELECT electricity_business_id FROM product_info WHERE 1=1 " +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(trading_time,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(trading_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(trading_time) = #{quarter}</if>" +
            ")" +
            "<if test=\"addressCode !=null and addressCode!=''\"> AND address_code LIKE #{addressCode}</if>" +
            ") AS t1 LEFT JOIN " +
            "(SELECT COUNT(*) AS existingAmount FROM  electricity_business  WHERE `type`=1 AND id  IN " +
            "(SELECT DISTINCT(electricity_business_id) FROM product_info WHERE trading_volume>0 " +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(trading_time,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(trading_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(trading_time) = #{quarter}</if>" +
            " GROUP BY electricity_business_id )" +
            "<if test=\"addressCode !=null and addressCode!=''\"> AND address_code LIKE #{addressCode}</if>" +
            ") AS t2 ON 1=1 " +
            "LEFT JOIN " +
            "(SELECT TRUNCATE(AVG(trading_volume),2) AS averageTurnover  FROM product_info WHERE trading_volume>0 AND electricity_business_id IN " +
            "(SELECT id FROM  electricity_business  WHERE `type`=1" +
            "<if test=\"addressCode !=null and addressCode!=''\"> AND address_code LIKE #{addressCode}</if>" +
            ")" +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(trading_time,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(trading_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(trading_time) = #{quarter}</if>" +
            ") AS t3 ON 1=1 LEFT JOIN " +
            "(SELECT COUNT(*) AS zeroAmount FROM  electricity_business  WHERE `type`=1 AND id  IN " +
            "(SELECT DISTINCT(electricity_business_id) FROM product_info WHERE electricity_business_id NOT IN" +
            "" +
            "(SELECT a.electricity_business_id FROM " +
            "" +
            "(SELECT electricity_business_id FROM product_info WHERE trading_volume>0" +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(trading_time,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(trading_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(trading_time) = #{quarter}</if>" +
            " GROUP BY electricity_business_id ) AS a," +
            "" +
            "(SELECT electricity_business_id FROM product_info WHERE trading_volume=0 " +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(trading_time,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(trading_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(trading_time) = #{quarter}</if>" +
            "GROUP BY electricity_business_id ) AS b WHERE " +
            "a.electricity_business_id=b.electricity_business_id" +
            ") AND trading_volume=0 " +
            "<if test=\"yearAndMonth !=null and yearAndMonth!=''\"> AND DATE_FORMAT(trading_time,'%Y-%m') = #{yearAndMonth}</if>" +
            "<if test=\"year !=null and year!=''\"> AND year(trading_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(trading_time) = #{quarter}</if>" +
            " )" +
            "<if test=\"addressCode !=null and addressCode!=''\"> AND address_code LIKE #{addressCode}</if>" +
            ") AS t4 ON 1=1 " +
            "</script>")
    public HashMap<String, Object> getThreePlatformStatistics(
            @Param("addressCode") String addressCode,
            @Param("yearAndMonth") String yearAndMonth,
            @Param("year") String year,
            @Param("quarter") String quarter
    );


    /**
     * 统计电商平台信息
     *
     * @param param
     * @return
     */
    @Select("<script>" +
            "SELECT e.platform_category AS platformCategory, e.address_code AS addressCode, g.geo_name AS addressDetail, e.type, " +
            "count(e.platform_category) AS count, DATE_FORMAT(e.create_time,'%Y-%m') AS createTime  " +
            "FROM electricity_business e LEFT JOIN geo_codings g ON e.address_code=g.id " +
            "<where>" +
            "<if test=\"param.addressCode !=null and param.addressCode!=''\"> AND e.address_code LIKE #{param.addressCode}</if>" +
            "<if test=\"param.yearAndMonth !=null and param.yearAndMonth!=''\"> AND DATE_FORMAT(e.create_time,'%Y-%m') = #{param.yearAndMonth}</if>" +
            "<if test=\"param.year !=null and param.year!=''\"> AND year(e.create_time) = #{param.year}</if>" +
            "<if test=\"param.quarter !=null and param.quarter!=''\"> AND quarter(e.create_time) = #{param.quarter}</if>" +
            "</where>" +
            "<if test=\"param.group!=null and param.group!=''\"> GROUP BY ${param.group} </if>" +
            "</script>")
    public List<HashMap<String, Object>> getPlatformStatistics(@Param("param") PlatformStatisticsParam param);


    /**
     * 查询归属平台所有
     * @return
     */
    @Select("<script>SELECT DISTINCT(platform_category) FROM electricity_business e where e.platform_category!='-1'" +
            "  <if test=\"type !=null and type!=''\">  and e.type=#{type}</if> " +
            "   <if test=\"addressCode !=null and addressCode!=''\"> AND e.address_code LIKE concat('%',#{addressCode},'%')</if> </script>")
    public List<String> getPlatformCategoryList(@Param("addressCode") String addressCode, @Param("type") String type);

    @Update("UPDATE electricity_business SET address_code = #{addressCode} WHERE id =#{id}")
    public int updateAddressCodeById(@Param("addressCode") String addressCode,@Param("id") String id);
}
